
import numpy as np 
# from numpy import NaN
from openpyxl import Workbook
from openpyxl import load_workbook
import pathlib
import easygui #pip install easygui
import os
import sqlite3
import decimal
from inc import *
import logging
#pip install pycryptodome bce-python-sdk
#pip install numpy openpyxl
#pip install easygui
sql="SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"
r=cur.execute(sql)

#print(r.fetchall())
data_=choiceStatistics()
统计科目=data_['subjects']
fullPoint=data_['fp']
data_=None
sql=r'DROP TABLE IF EXISTS "base";'
cur.execute(sql)
sql=r'DROP VIEW  IF EXISTS "bj"; '
cur.execute(sql)
sql=""
for i in 统计科目:
    sql=sql+f", \"{i}\" real "

sql=f'CREATE TABLE "base" ( "ID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,  "姓名" text , "班级" text {sql} ,"总分" real);'
cur.execute(sql)
sql=r'CREATE VIEW "main"."bj" AS SELECT base."班级" FROM base GROUP BY base."班级" ORDER BY base."班级";'
cur.execute(sql)     
db.commit()
'''
for dir_path, subpaths, files in os.walk(root, True):
    for file in files:
        file=root/pathlib.Path(file)
        suf=file.suffix
        print(str(file))
        print(file.name[0])
        if(suf==".xlsx" and file.name[0]!="~"):
            wb = load_workbook(str(file))
            break
'''

filetypes = ["*.xlsx"]
default=str(root)+"/*.xlsx"
opened_files = easygui.fileopenbox(None,None,default,filetypes,multiple=True)
# opened_files =['D:\\git\\python\\cjtj\\9y.xlsx']
try:
    wb = load_workbook(str(opened_files[0]))
except:
    easygui.exceptionbox("文件读取错误")
    exit()
for sheet in wb:
    titleIndex={}
    for i in range(1,sheet.max_column+1):
        if(sheet.cell(row=2, column=i).value in (统计科目+["姓名", "班级"])):
            titleIndex[sheet.cell(row=2, column=i).value]=i
        # print(sheet.cell(row=2, column=i).value,i)
    print(titleIndex)
    for i in 统计科目:
        if(i not in titleIndex):
            logging.error(f"找不到【{i}】科目成绩！")
            input("")
            sys.exit()
    for i in range(3,sheet.max_row+1):
        if(not (sheet.cell(row=i, column=titleIndex['姓名']).value==None)):
            try:
                data=[]
                for n in 统计科目 :
                    data.append(convert(sheet.cell(row=i, column=titleIndex[n]).value,n),)
            except:
                logging.error(f"{sheet.cell(row=i, column=titleIndex['班级']).value}{sheet.cell(row=i, column=titleIndex['姓名']).value}[{n}]\"{sheet.cell(row=i, column=titleIndex[n]).value}\"数据有误！")
                input("")
                sys.exit()

            data=[np.nan if i=="缺考" or i == None else i for i in data]
            try:
                
                zf=np.array(data,dtype=np.double)
                # np.set_printoptions(precision=8)
            except:
                easygui.exceptionbox("{bj}第{row}行  {name}  的数据有误，请检查！".format(bj=sheet.cell(row=i, column=2).value,row=i,name=sheet.cell(row=i, column=3).value))
                exit()
            for i1 in range(7):
                if(data[i1]!=None):
                    zft=np.round(np.nansum(zf),8)
                    # zf=np.nansum(zf)
                    break
                else:
                    zft="null"
            zf=zft
            zft=None
            # t=decimal.Decimal(str(sheet.cell(row=i, column=13).value))+decimal.Decimal(str(sheet.cell(row=i, column=12).value))
            # if(decimal.Decimal(zf)==t or zf==decimal.Decimal('0')):
            #     zf="null"
            sql1=""
            sql2=""
            for n in 统计科目:
                sql1=sql1+f", `{n}` "
                if(data[统计科目.index(n)] is np.nan):
                    sql2=f"{sql2} , {"NULL"}"
                else:
                    sql2=f"{sql2} , {data[统计科目.index(n)]}"
            name=sheet.cell(row=i, column=titleIndex['姓名']).value.replace(" ","").replace(" ","")
            bj=f"{sheet.cell(row=i, column=titleIndex['班级']).value}班".replace("班班","班")
            if np.isclose(zf,0,rtol=1e-10, atol=1e-12) and MissedTest is None : zf="缺考"
            sql=f'INSERT INTO base(`姓名` , `班级` {sql1} , 总分) VALUES ("{name}","{bj}" {sql2} , {zf})'.replace("None","null")
            sql=sql.replace("缺考","null")
            print(sql)
            cur.execute(sql)
            
            sql1=None
            sql2=None
        else:
            #print("none")
            pass
    db.commit()


bj=getbj()
#print(len(r))
list={}
for i in bj:
    #print(i)
    r_1=cur.execute(sqlbj(i))
    listbj=[]
    for x in r_1.fetchall():
        #print(x)
        listbj.append(x)
    list[i]=np.asarray(listbj, dtype = np.double)  



   
f=['平均分','最高分','最低分','标准差'] 
data={}
for i in f:
    t=[]
    for n in bj:
        t.append(calc(list[n],i))
    data[i]=t
        
print(data)
'''
html=""
for n in f:
    html+="<tr><td style=\"vertical-align: middle;\" rowspan=\"{count_}\"><div style=\"width: 20px;line-height: 18px; margin:0 auto;\">{vt}</div></td>".format(count_=len(list),vt=n)
    for i in list:
        print(i,n)
        r=calc(list[i],n)
        print(r)
        html+="<td>{x}</td>".format(x=i)
        for x in r:
            html+="<td>{x:.3f}</td>".format(x=x)
        html+="</tr><tr>"
    html.rstrip("<tr>")
with open(root/'index.xml','r+',encoding='utf8') as f :
    text=f.read()
text=text.replace(r"<!--content1-->",html)
with open(root/'index.html','w+',encoding='utf8') as f :
    f.write(text)
'''

f=['总人数','及格人数','优秀人数','低分人数','中间人数']
for i in f :
    t=[]
    for n in bj:
        t.append(counts(i,n))
    data[i]=np.array(t)
data['及格率']=np.true_divide(data['及格人数'],data['总人数'])
data['优秀率']=np.true_divide(data['优秀人数'],data['总人数'])
data['低分率']=np.true_divide(data['低分人数'],data['总人数'])
data['中间率']=np.true_divide(data['低分人数'],data['总人数'])
print(data)

# f={'最高分':"{x:.1f}",'最低分':"{x:.1f}",'及格率':"{x:.2%}",'优秀率':"{x:.2%}",'低分率':"{x:.2%}",'中间率':"{x:.2%}",'及格人数':"{x:.0f}",'优秀人数':"{x:.0f}",'低分人数':"{x:.0f}",'总人数':"{x:.0f}"}
# gethtml(data,f)
# getDetailed()
f={'最高分':"0.0",'最低分':"0.0",'及格率':"0.00%",'优秀率':"0.00%",'低分率':"0.00%",'中间率':"0.00%",'及格人数':"0",'优秀人数':"0",'低分人数':"0",'总人数':"0",'中间人数':"0"}
genXlsx(data,f,opened_files[0])
easygui.msgbox("已完成！数据保存在 {0}统计.xlsx".format((str(pathlib.Path(opened_files[0]).parent)+"/"+pathlib.Path(opened_files[0]).stem).replace("\\",'/')))
